-- This scripts contains following function's definition:
-- =============================================================================
-- NULL 相关函数
-- 注意： ifnull 效果等同于通用函数 coalesce，在条件允许的情况下，建议修改 SQL 使用 coalesce 函数，该函数几乎在各个数据库中均支持
-- ifnull(text,text)
-- ifnull(numeric,numeric)
-- ifnull(timestamp,timestamp)
-- ifnull(timestamptz,timestamptz)

-- 条件控制函数
-- if(bool,text,text)
-- if(bool,numeric,numeric)
-- if(bool,timestamptz,timestamptz)

-- 字符函数
-- find_in_set(text,text)
-- find_in_set(text,text[])
-- field(text,text[])
-- elt(int4,text[])
-- strcmp(text,text)
-- insert(text,int8,int8,text)
-- lcase(text)
-- ucase(text)
-- space(int4)
-- mid(text,int8,int8)
-- locate(text,text,int4)
-- to_base64(text)
-- from_base64(text)

-- 数字函数
-- field(numeric,numeric[])
-- log10(numeric)
-- log10(float8)
-- rand(int4)

-- 时间函数
-- unix_timestamp(date)
-- unix_timestamp(timestamp)
-- unix_timestamp(timestamptz)
-- from_unixtime(int8)
-- from_unixtime(numeric)
-- from_unixtime(numeric,text)
-- to_days(timestamp)
-- to_seconds(date)
-- to_seconds(timestamp)
-- to_seconds(timestamptz)
-- timediff(timestamptz,timestamptz)
-- time_to_sec(time)
-- sec_to_time(int4)

-- todo
-- =============================================================================
-- conv(n,from_base,to_base)  -- 数字进制转换


set client_min_messages='warning';


-- =============================================================================
-- Create Schema
-- =============================================================================
do $$
declare
    l_cnt       bigint;
    l_version   varchar(10);
begin
    set client_min_messages='warning';
    select count(*) into l_cnt from pg_catalog.pg_namespace where nspname = 'oracle';
    if l_cnt = 0
    then
        create schema oracle;
    end if;
end;
$$ language plpgsql;


-- =============================================================================
-- Version Table/Function
-- =============================================================================
create table if not exists oracle.compat_version
(
    compat_type     varchar(10),    -- VIEW, FUNCTION
    object_name     varchar(128),   -- Compat object name
    object_version  varchar(10),    -- Compat object version
    constraint pk_compat_version primary key(compat_type, object_name)
);

drop function if exists oracle.drop_compat_function(varchar, varchar, varchar, varchar, varchar);
create or replace function oracle.drop_compat_function( p_object_type    varchar(10)
                                                      , p_object_name    varchar(128)
                                                      , p_object_version varchar(10)
                                                      , p_function_lang  varchar(16)  default 'sql'
                                                      , p_object_schema  varchar(128) default 'pg_catalog')
returns boolean
as $$
declare
    l_version    varchar(10);
    l_language   varchar(16);
    l_result     boolean      := 'true';
    l_operation  varchar(128);
begin
    CREATE temporary table if not exists temp_result
    (
        object_type     varchar(10),
        object_name     varchar(128),
        local_version   varchar(10),
        script_version  varchar(10),
        local_language  varchar(10),
        script_language varchar(10),
        operation       varchar(128)
    );

    select max(object_version) into l_version
      from oracle.compat_version
     where object_name = lower(p_object_name)
       and compat_type = lower(p_object_type);

    if instr(p_object_name, '()') = 0    -- has parameters
    then
        select max(lanname) into l_language
          from (select l.lanname
                     , p.proname || '(' || string_agg(case when a.typname is not null then a.typname||'[]' else t.typname end, ',' order by p.id) || ')' as full_name
                  from (select pronamespace
                             , proname
                             , prolang
                             , proargtypes
                             , generate_series(1, array_length(proargtypes, 1)) as id
                             , unnest(proargtypes) as protype
                          from pg_proc
                         where proname = substr(p_object_name, 1, instr(p_object_name, '(') - 1)
                       ) as p
                  join pg_namespace as n on p.pronamespace = n.oid
                  join pg_language as l on p.prolang = l.oid
                  join pg_type as t on p.protype = t.oid
                  left join pg_type as a on t.typcategory = 'A' and t.typelem = a.oid   -- for array type
                 where n.nspname = p_object_schema
                 group by p.proname, p.proargtypes, l.lanname
               ) as x
         where full_name = p_object_name;
    else    -- no parameter
        select max(l.lanname) into l_language
          from pg_proc as p
          join pg_namespace as n on p.pronamespace = n.oid
          join pg_language as l on p.prolang = l.oid
         where n.nspname = p_object_schema
           and p.proname = replace(p_object_name, '()', '')
           and oidvectortypes(p.proargtypes) is null;
    end if;

    if l_language is null
    then
        l_operation := 'Initial creation';
    elsif l_language != p_function_lang
    then
        l_result = 'false';
        l_operation := 'Skip due to language';
    elsif l_version < p_object_version
    then
        l_operation := 'Upgrade';
    else
        l_result = 'false';
        l_operation := 'Skip due to version';
    end if;

    if l_result
    then
        execute 'drop '||p_object_type||' if exists '||p_object_schema||'.'||p_object_name||' cascade';
        insert into oracle.compat_version
        values (p_object_type, p_object_name, p_object_version)
        ON DUPLICATE KEY UPDATE object_version = p_object_version;
    else
        insert into oracle.compat_version
        values (p_object_type, p_object_name, p_object_version)
        ON DUPLICATE KEY UPDATE NOTHING;
    end if;

    insert into temp_result values ( p_object_type
                                   , p_object_name
                                   , l_version
                                   , p_object_version
                                   , l_language
                                   , p_function_lang
                                   , l_operation);
    return l_result;
end;
$$ language plpgsql;


-- 检测数据库内部的函数带参数的全程（无参数函数不显示）
    -- select l.lanname
    --      , p.proname || '(' || string_agg(case when a.typname is not null then a.typname||'[]' else t.typname end, ',' order by p.id) || ')' as full_name
    --   from (select pronamespace
    --              , proname
    --              , prolang
    --              , proargtypes
    --              , generate_series(1, array_length(proargtypes, 1)) as id
    --              , unnest(proargtypes) as protype
    --           from pg_proc
    --          where proname = 'insert'
    --        ) as p
    --   join pg_namespace as n on p.pronamespace = n.oid
    --   join pg_language as l on p.prolang = l.oid
    --   join pg_type as t on p.protype = t.oid
    --   left join pg_type as a on t.typcategory = 'A' and t.typelem = a.oid
    --  where n.nspname = 'pg_catalog'
    --  group by p.proname, p.proargtypes, l.lanname;



-- =============================================================================
-- All creations are running in ANONYMOUS BLOCK
-- =============================================================================
do $VIEW_CREATION$
declare
    l_cnt    bigint;
begin
    -- =========================================================================
    -- 注意： ifnull 效果等同于 coalesce 函数，在条件允许的情况下，建议修改 SQL 使用 coalesce 函数，几乎在各个数据库中均支持
    -- ifnull(text,text)
    -- ifnull(numeric,numeric)
    -- ifnull(timestamp,timestamp)
    -- ifnull(timestamptz,timestamptz)
    -- 测试用例：
    --     select pg_typeof(ifnull(123, 1111)), ifnull(123, 1111), ifnull(null, 1111), ifnull(123, null), ifnull(null, null);
    --     select pg_typeof(ifnull('xyz', 'abc')), ifnull('xyz', 'abc'), ifnull(null, 'abc'), ifnull('xyz', null), ifnull(null, null);
    --     select pg_typeof(ifnull(now(), now())), ifnull(now(), now()), ifnull(null, now()), ifnull(now(), null), ifnull(null, null);
    --     select pg_typeof(ifnull('xyz', 1111)), ifnull('xyz', 1111), ifnull(null, 1111), ifnull('xyz', null), ifnull(null, null);
    -- =========================================================================
    if oracle.drop_compat_function('function', 'ifnull(text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.ifnull(p1 text, p2 text)
        RETURNS text IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'ifnull(numeric,numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.ifnull(p1 numeric, p2 numeric)
        RETURNS numeric IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'ifnull(timestamp,timestamp)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.ifnull(p1 timestamp, p2 timestamp)
        RETURNS timestamp IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'ifnull(timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.ifnull(p1 timestamptz, p2 timestamptz)
        RETURNS timestamptz IMMUTABLE AS $$
        SELECT coalesce(p1, p2);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- find_in_set(text,text)
    -- find_in_set(text,text[])
    -- 测试用例：
    --     两个参数均为字符串：
    --     select find_in_set('b','a,b,c,dx') as c1, find_in_set('x','a,b,c,dx') as c2, find_in_set('d','a,b,c,dx') as c3, find_in_set('dx','a,b,c,dx') as c4;
    --     select find_in_set(null,'a,b,c,dx') as c1, find_in_set('x',null) as c2, find_in_set('','a,b,c,dx') as c3, find_in_set('x','') as c4, find_in_set('','a,b,c,,dx') as c5;
    --     参数为字符串与数组：
    --     select find_in_set('b',string_to_array('a,b,c,dx', ',')) as c1, find_in_set('x',string_to_array('a,b,c,dx', ',')) as c2, find_in_set('d',string_to_array('a,b,c,dx', ',')) as c3, find_in_set('dx',string_to_array('a,b,c,dx', ',')) as c4;
    --     select find_in_set(null,string_to_array('a,b,c,dx', ',')) as c1, find_in_set('',string_to_array('a,b,c,dx', ',')) as c3, find_in_set('x',string_to_array('', ',')) as c4, find_in_set('',string_to_array('a,b,c,,dx', ',')) as c5;
    -- =========================================================================
    if oracle.drop_compat_function('function', 'find_in_set(text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.find_in_set(str text, strlist text)
        RETURNS bigint IMMUTABLE AS $$
        select case when str is null or strlist is null then null
                    else (select coalesce(min(id), 0) from (select rownum as id, unnest(string_to_array(strlist, ',')) as elem) as x where elem = str)
               end;
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'find_in_set(text,text[])', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.find_in_set(str text, strlist text[])
        RETURNS bigint IMMUTABLE AS $$
        select case when str is null or strlist is null then null
                    else (select coalesce(min(id), 0) from (select rownum as id, unnest(strlist) as elem) as x where elem = str)
               end;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- field(text,VARIADIC text[])
    -- field(numeric,VARIADIC numeric[])
    -- 测试用例：
    --     select field('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
    --     select field('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
    --     select field(123, 1.23, 12.3, 123, 1234, 0.12);
    --     select field(123, 1.23, 12.3,  23, 1234, 0.12);
    -- =========================================================================
    if oracle.drop_compat_function('function', 'field(text,text[])', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.field(str text, VARIADIC strlist text[])
        RETURNS bigint IMMUTABLE AS $$
        select case when str is null or strlist is null then 0
                    else (select coalesce(min(id), 0) from (select rownum as id, unnest(strlist) as elem) as x where elem = str)
               end;
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'field(numeric,numeric[])', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.field(str numeric, VARIADIC strlist numeric[])
        RETURNS bigint IMMUTABLE AS $$
        select case when str is null or strlist is null then 0
                    else (select coalesce(min(id), 0) from (select rownum as id, unnest(strlist) as elem) as x where elem = str)
               end;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- elt(int4,VARIADIC text[])
    -- 测试用例：
    --     select elt( 2, 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
    --     select elt(1.2, 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
    --     select elt( 0, 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
    --     select elt(10, 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
    -- =========================================================================
    if oracle.drop_compat_function('function', 'elt(int4,text[])', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.elt(str_pos int, VARIADIC strlist text[])
        RETURNS text IMMUTABLE AS $$
        select min(elem) from (select rownum as id, unnest(strlist) as elem) as x where id = str_pos;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- if(bool,text,text)
    -- if(bool,numeric,numeric)
    -- if(bool,timestamptz,timestamptz)
    -- 测试用例：
    --     select if(1<2,'yes','no');
    --     select if('hongye'!='debug',1,2);
    --     select if(instr('hongye', 'x'), now(), now() - interval '1 day');
    -- =========================================================================
    if oracle.drop_compat_function('function', 'if(bool,text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.if(check_condition boolean, true_expr text, false_expr text)
        RETURNS text IMMUTABLE AS $$
        select case when check_condition then true_expr else false_expr end;
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'if(bool,numeric,numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.if(check_condition boolean, true_expr numeric, false_expr numeric)
        RETURNS numeric IMMUTABLE AS $$
        select case when check_condition then true_expr else false_expr end;
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'if(bool,timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.if(check_condition boolean, true_expr timestamptz, false_expr timestamptz)
        RETURNS timestamptz IMMUTABLE AS $$
        select case when check_condition then true_expr else false_expr end;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- strcmp(text,text)
    -- 测试用例：
    --     select strcmp('text', 'text2');  -- -1
    --     select strcmp('text2', 'text');  -- 1
    --     select strcmp('text', 'text');   -- 0
    -- =========================================================================
    if oracle.drop_compat_function('function', 'strcmp(text,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.strcmp(p1 text, p2 text)
        RETURNS int IMMUTABLE AS $$
        select case when p1 > p2 then 1 when p1 = p2 then 0 else -1 end;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- log10(numeric)
    -- log10(float8)
    -- 测试用例：
    --     select log10(2);     -- 0.30102999566398
    --     select log10(100);   -- 2
    --     select log10(-100);  -- NULL
    -- =========================================================================
    if oracle.drop_compat_function('function', 'log10(numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.log10(p1 numeric)
        RETURNS numeric IMMUTABLE AS $$
        select case when p1 <= 0 then null else pg_catalog.log(p1) end;
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'log10(float8)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.log10(p1 double precision)
        RETURNS double precision IMMUTABLE AS $$
        select case when p1 <= 0 then null else pg_catalog.log(p1) end;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- insert(text,int8,int8,text)
    -- 测试用例：
    --     select insert('Quadratic', 3, 4, 'What');       -- QuWhattic
    --     select insert('Quadratic', -1, 4, 'What');      -- Quadratic
    --     select insert('Quadratic', 0, 2, 'What');       -- Quadratic
    --     select insert('Quadratic', 1, 2, 'What');       -- Whatadratic
    --     select insert('Quadratic', 9, 12, 'What');      -- QuadratiWhat
    --     select insert('Quadratic', 3, 100, 'What');     -- QuWhat
    --     select insert('Quadratic', 3, 100, null);       -- NULL
    --     select insert('Quadratic', null, 100, 'What');  -- NULL
    -- =========================================================================
    if oracle.drop_compat_function('function', 'insert(text,int8,int8,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.insert(p_source text, p_pos bigint, p_len bigint, p_replacement text)
        RETURNS text IMMUTABLE AS $$
        select case when p_source is null or p_pos is null or p_len is null or p_replacement is null then null
                    when p_pos <= 0 or p_pos > length(p_source) then p_source
                    else pg_catalog.substr(p_source, 1, p_pos - 1)||p_replacement||pg_catalog.substr(p_source, p_pos + p_len)
               end;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- lcase(text)
    -- 测试用例：
    --     select lcase('QuadRatic');       -- quadratic
    -- =========================================================================
    if oracle.drop_compat_function('function', 'lcase(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.lcase(p_source text)
        RETURNS text IMMUTABLE AS $$
        select pg_catalog.lower(p_source);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- ucase(text)
    -- 测试用例：
    --     select ucase('QuadRatic');       -- QUADRATIC
    -- =========================================================================
    if oracle.drop_compat_function('function', 'ucase(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.ucase(p_source text)
        RETURNS text IMMUTABLE AS $$
        select pg_catalog.upper(p_source);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- space(int4)
    -- 测试用例：
    --     select concat('|', space(11), '|');   -- |           |
    --     select concat('|', space(-11), '|');  -- ||
    --     select concat('|', space(0), '|');    -- ||
    -- =========================================================================
    if oracle.drop_compat_function('function', 'space(int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.space(p_num int)
        RETURNS text IMMUTABLE AS $$
        select pg_catalog.repeat(' ', p_num);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- mid(text,int8,int8)
    -- 测试用例：
    --     select mid('Hongye', 2, 4);   -- 'ongy'
    --     select mid('Hongye', -2, 4);  -- 'ye'
    --     select mid('Hongye', 12, 4);  -- ''
    --     select mid('Hongye', 2, -4);  -- ''
    -- =========================================================================
    if oracle.drop_compat_function('function', 'mid(text,int8,int8)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.mid(p_source text, p_pos bigint, p_len bigint)
        RETURNS text IMMUTABLE AS $$
        select pg_catalog.substr(p_source, p_pos, p_len);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- locate(text,text,int4)
    -- 测试用例：
    --     select locate('bar', 'foobarbar');       -- 4
    --     select locate('xbar', 'foobar');         -- 0
    --     select locate('bar', 'foobarbar', 5);    -- 7
    --     select locate('bar', 'foobarbar', 7);    -- 7
    --     select locate(null, 'foobarbar', 7);     -- NULL
    --     select locate('ba', 'foobarbar', null);  -- NULL
    -- =========================================================================
    if oracle.drop_compat_function('function', 'locate(text,text,int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.locate(p_sub text, p_source text, p_pos int default 1)
        RETURNS int IMMUTABLE AS $$
        select pg_catalog.instr(p_source, p_sub, p_pos);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- unix_timestamp(timestamp)
    -- unix_timestamp(timestamptz)
    -- 测试用例：
    --     select unix_timestamp();
    --     select unix_timestamp(sysdate);
    --     select unix_timestamp(systimestamp);
    -- =========================================================================
    if oracle.drop_compat_function('function', 'unix_timestamp(timestamp)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.unix_timestamp(p_date timestamp)
        RETURNS double precision IMMUTABLE AS $$
        select extract(epoch from p_date);
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'unix_timestamp(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.unix_timestamp(p_date timestamptz)
        RETURNS double precision IMMUTABLE AS $$
        select extract(epoch from p_date);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- from_unixtime(int8)
    -- from_unixtime(numeric)
    -- from_unixtime(numeric,text)
    -- 测试用例：
    --     select from_unixtime(123456789);
    --     select from_unixtime(123456789.123456);
    --     select from_unixtime(123456789.123456, 'YYYY.MM.DD HH24.MI.SS');
    -- =========================================================================
    if oracle.drop_compat_function('function', 'from_unixtime(int8)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.from_unixtime(p_ts bigint)
        RETURNS date IMMUTABLE AS $$
        select to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1 seconds' * p_ts;
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'from_unixtime(numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.from_unixtime(p_ts numeric)
        RETURNS timestamp IMMUTABLE AS $$
        select to_timestamp('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1 seconds' * p_ts;
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'from_unixtime(numeric,text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.from_unixtime(p_ts numeric, p_format text)
        RETURNS text IMMUTABLE AS $$
        select to_char(to_timestamp('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + interval '1 seconds' * p_ts, p_format);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- to_days(timestamp)
    -- 测试用例：
    --     select to_days('2020-02-02');  -- 737822
    --     select to_days(to_date('2020-02-02', 'YYYY-MM-DD'));  -- 737822
    -- =========================================================================
    if oracle.drop_compat_function('function', 'to_days(timestamp)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.to_days(p_date timestamp)
        RETURNS bigint IMMUTABLE AS $$
        SELECT floor(extract(epoch from p_date)/86400)::bigint + 719528;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- to_seconds(timestamp)
    -- to_seconds(timestamptz)
    -- 测试用例：
    --     select to_seconds(950501);                 -- 62966505600 (暂时不支持)
    --     select to_seconds('2009-11-29');           -- 63426672000
    --     select to_seconds('2009-11-29 13:43:32');  -- 63426721412
    --     select to_seconds( now() );
    -- =========================================================================
    if oracle.drop_compat_function('function', 'to_seconds(timestamp)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.to_seconds(p_date timestamp)
        RETURNS bigint IMMUTABLE AS $$
        SELECT floor(extract(epoch from p_date))::bigint + 62167219200;
        $$ LANGUAGE sql;
    end if;
    if oracle.drop_compat_function('function', 'to_seconds(timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.to_seconds(p_date timestamptz)
        RETURNS bigint IMMUTABLE AS $$
        SELECT floor(extract(epoch from p_date))::bigint + 62167219200;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- timediff(timestamptz,timestamptz)
    -- 测试用例：
    --     select timediff('2000-01-01 00:00:00', '2000-01-01 00:00:00.000001');         -- -00:00:00.000001
    --     select timediff('2008-12-31 23:59:59.000001', '2008-12-30 01:01:01.000002');  -- 1 day 22:58:57.999999
    -- =========================================================================
    if oracle.drop_compat_function('function', 'timediff(timestamptz,timestamptz)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.timediff(p_date1 timestamptz, p_date2 timestamptz)
        RETURNS interval IMMUTABLE AS $$
        SELECT p_date1 - p_date2;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- time_to_sec(time)
    -- 测试用例：
    --     select time_to_sec('22:23:00');  -- 80580
    --     select time_to_sec('00:39:38');  -- 2378
    -- =========================================================================
    if oracle.drop_compat_function('function', 'time_to_sec(time)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.time_to_sec(p_time time)
        RETURNS int IMMUTABLE AS $$
        select extract(epoch from p_time::time)::int;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- sec_to_time(int4)
    -- 测试用例：
    --     select sec_to_time(80580);  -- 22:23:00
    --     select sec_to_time(2378);   -- 00:39:38
    --     select sec_to_time(805801);  -- 9 days, 7:50:01
    --     select sec_to_time(8058011);  -- MySQL (34 days, 22:59:59) 达到上限了，实际 3 mons 3 days 06:20:11
    -- =========================================================================
    if oracle.drop_compat_function('function', 'sec_to_time(int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.sec_to_time(p_secs int)
        RETURNS interval IMMUTABLE AS $$
        select justify_interval(numtodsinterval(p_secs, 'second'));
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- to_base64(text)
    -- 测试用例：
    --     select to_base64('abc');     -- YWJj
    --     select to_base64('Hongye');  -- SG9uZ3ll
    -- =========================================================================
    if oracle.drop_compat_function('function', 'to_base64(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.to_base64(p_str text)
        RETURNS text IMMUTABLE AS $$
        select encode(p_str::bytea, 'base64'::text);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- from_base64(text)
    -- 测试用例：
    --     select from_base64('YWJj');      -- abc
    --     select from_base64('SG9uZ3ll');  -- Hongye
    -- =========================================================================
    if oracle.drop_compat_function('function', 'from_base64(text)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.from_base64(p_str text)
        RETURNS text IMMUTABLE AS $$
        select convert_from(decode(p_str, 'base64'), 'UTF8');
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- truncate(numeric,int4)
    -- 测试用例：
    --     select truncate(1.223,1);      -- 1.2
    --     select truncate(1.999, 1);     -- 1.9
    --     select truncate(1.999, 0);     -- 1
    --     select truncate(1.999, -1);    -- 0
    --     select truncate(121.999, -1);  -- 120
    -- =========================================================================
    if oracle.drop_compat_function('function', 'truncate(numeric,int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.truncate(p_num numeric, p_prec int4)
        RETURNS numeric IMMUTABLE AS $$
        select trunc(p_num, p_prec);
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- rand_setseed(int4)
    -- rand(int4)
    -- 测试用例：
    --     select rand();      -- 多次查询结果不同，均在 (0, 1) 之间
    --     select rand(123);   -- 多次查询结果相同，均在 (0, 1) 之间
    --     select rand() from information_schema.tables limit 11;     -- 多次查询结果不同
    --     select rand(123) from information_schema.tables limit 11;  -- 多次查询结果相同
    -- =========================================================================
    if oracle.drop_compat_function('function', 'rand_setseed(int4)', '1.0', 'plpgsql')
    then
        CREATE or replace FUNCTION pg_catalog.rand_setseed(p_seed int)
        RETURNS double precision IMMUTABLE AS $$
        begin
            perform setseed(p_seed/4294967296::float8);
            return 0;
        end;
        $$ LANGUAGE plpgsql;
    end if;
    if oracle.drop_compat_function('function', 'rand(int4)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.rand(p_seed int default null)
        RETURNS double precision AS $$
        select case when p_seed is null then random() else rand_setseed(p_seed) + random() end;
        $$ LANGUAGE sql;
    end if;


    -- =========================================================================
    -- log2(numeric)
    -- 测试用例：
    --     select log2(64);      -- 6.0
    --     select log2(123);     -- 6.94251450533924
    --     select log2(0);       -- null
    --     select log2(-12);     -- null
    -- =========================================================================
    if oracle.drop_compat_function('function', 'log2(numeric)', '1.0')
    then
        CREATE or replace FUNCTION pg_catalog.log2(p_num numeric)
        RETURNS numeric IMMUTABLE AS $$
        select case when p_num <= 0 then null else log(2, p_num) end;
        $$ LANGUAGE sql;
    end if;

end;
$VIEW_CREATION$ language plpgsql;

-- Show result & Exit
select * from temp_result where exists (select 1 from pg_settings where name = 'application_name' and setting = 'gsql');
\q
